﻿using Dapper;
using Lifang.Common;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Lifang.Dao
{
    public class CommonQuery
    {

        #region 分页 传Key

        /// <summary>
        ///  View分页 传入dmlKey
        /// </summary>
        /// <typeparam name="T">视图</typeparam>
        /// <param name="key">dmlKey</param>
        /// <param name="nvc">上下文</param>
        /// <param name="dp">分页</param>
        /// <returns>视图列表</returns>
        public List<T> SelectViewPagerByKey<T>(string key, NameValueCollection nvc, DataPage dp = null) where T : new()
        {
            var sql = new SysDmlDao().GetLongCotentByKey(key);
            if(!string.IsNullOrEmpty(nvc["Title"]))
            {
                //sql += string.Format(" AND Title LIKE '%@Title%'");
                sql += string.Format(" AND Title LIKE '%{0}%'",nvc["Title"]);
            }
            if (!string.IsNullOrEmpty(nvc["SubTitle"]))
            {
                //sql += " AND SubTitle LIKE '%@SubTitle%'";
                sql += string.Format(" AND SubTitle LIKE '%{0}%'", nvc["SubTitle"]);
            }
            var list = SelectViewPager<T>(sql, nvc, dp);
            return list;
        }


        #endregion

        #region 分页传SQL
        public List<T> SelectViewPager<T>(string sql, NameValueCollection nvc, DataPage dp = null)
        {
            var list = new List<T>();
            var sqlPagerOnly = string.Empty;


            //从Sql语句提取需要的参数
            List<string> selectItemList = GetSqlParametersList(sql); //此处不含 分页参数 

            if (dp != null)
            {
                //分页一定要orderby 
                if (string.IsNullOrWhiteSpace(dp.OrderField))
                {
                    //if (selectItemList.Count == 0)
                    //{
                    //    return new List<T>();
                    //}
                    //取Select里含“Id”的字段
                    if (selectItemList.Count > 0 && (selectItemList.Where(x => x.IndexOf("Id") > -1)).Any())
                    {
                        var ord = selectItemList.Where(x => x.IndexOf("Id") > -1).FirstOrDefault();
                        dp.OrderField = ord + " DESC ";
                    }
                    else
                    {
                        dp.OrderField = selectItemList.FirstOrDefault() + " DESC ";
                    }
                }
                //暂时先拼 c# 6.0新特性 formmat 或 传参数方式
                if (dp.PageSize == 0)
                {
                    dp.PageSize = 100;
                }

                string toAddPagerOnly = string.Format(" ORDER BY  {0}  limit {1},{2}", dp.OrderField, (dp.PageIndex - 1) * dp.PageSize, dp.PageSize);

                //var reg = new Regex(@"select\s+.*?\s+from\s+", RegexOptions.IgnoreCase);
                //string field = reg.Match(sql).Value;  // select .. From  含 select from 
                ////field = Regex.Replace(field.ToLower(), @"select\s+|\s+from\s+|\[|\]", "");
                //sqlPagerOnly = sql.Replace(field, " SELECT COUNT(1) AS RowNum FROM ");

                var reg = new Regex(@"select\s+[\s\S]*?\s+from\s+", RegexOptions.IgnoreCase);
                string field = reg.Match(sql).Value;  // select .. From  含 select from 
                sqlPagerOnly = sql.Replace(field, "SELECT   COUNT(1) AS RowNum  FROM ");

                sql = string.Format("{0} {1}", sql, toAddPagerOnly);

            }

            //从条件中提取 SqlParams
            object pm = GetSmartParamFormNvc(nvc, selectItemList);
            var conn = new DapperHelper().GetDBConnection();
            using (conn)
            {
                list = conn.Query<T>(sql, pm).ToList();

                if (dp != null)
                {
                    dp.RowCount = conn.Query<int>(sqlPagerOnly, pm).SingleOrDefault<int>();
                }

                return list ?? new List<T>();
            }

        }
        #endregion

        #region 

        #region 从NameValueCollection中提取Param
        private dynamic GetSmartParamFormNvc(NameValueCollection nvc, List<string> selectItemList)
        {
            var pm = new Dictionary<string, object>();
            foreach (var item in selectItemList)
            {
                if (nvc.AllKeys.Contains(item.ToLower(), new EqualIgnoreCase()))
                {
                    pm.Add(item, nvc[item.ToLower()]);
                }
                else
                {
                    pm.Add(item, null);
                }
            }
            return pm as dynamic;
        }

        #endregion

        #region 从SQL中提取参数
        private static readonly Regex SqlParameterRegex = new Regex("@\\w+", RegexOptions.Compiled);
        #endregion

        #region 从SQL中提取 @参数
        /// <summary>
        /// 从SQL中提取 @参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<string> GetSqlParametersList(string sql)
        {
            List<string> list = new List<string>();
            MatchCollection matchCollection = SqlParameterRegex.Matches(sql);
            foreach (Match match in matchCollection)
            {
                list.Add(match.Value.TrimStart(new char[]
                {
                    '@'
                }));
            }
            return list.Distinct().ToList();
        }
        #endregion

        #region 不区分大小写
        /// <summary>
        /// 不区分大小写
        /// </summary>
        public class EqualIgnoreCase : IEqualityComparer<string>
        {
            public bool Equals(string x, string y)    //比较x和y对象是否相同，按照地址比较
            {
                return x.ToLower() == y.ToLower();
            }

            public int GetHashCode(string obj)
            {
                return obj.ToString().GetHashCode();
            }
        }
        #endregion

        #endregion
    }
}
